#include "sqlmanagement.h"
#include <mysql_driver.h>
#include <mysql_connection.h>


SQLManagement::SQLManagement() {}

SQLManagement::SQLManagement(const QString &host, const QString &user, const QString &password, const QString &database)
    : host(host), database(database), user(user), password(password), driver(nullptr), con(nullptr)
{
    if (!connect()) {
        LOG_ERROR() << "connect to mysql failed";
    } else {
        LOG_INFO() << "init mysql connection success";
    }
}

SQLManagement::~SQLManagement() {
    disconnect();
}

bool SQLManagement::connect() {
    try {
        // connect to mariadb
        driver = sql::mysql::get_driver_instance();
        con = driver->connect(host.toStdString(), user.toStdString(), password.toStdString());
        con->setSchema(database.toStdString());
        LOG_INFO() << "[mysql connect success!]";
        return true;
    } catch (sql::SQLException &e) {
        LOG_ERROR() << "[mysql connect failed!]: " << e.what();
        return false; // 返回 false 表示连接失败
    }
}

void SQLManagement::disconnect(){
    if(con) {
        delete con;
        con = nullptr;
        LOG_INFO() << "mysql connection closed.";
    }
}

bool SQLManagement::executeQuery(const QString &query) {
    try {
        std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(query.toStdString()));
        pstmt->execute();
        LOG_INFO() << "query success";
        return true;
    } catch (sql::SQLException &e) {
        LOG_ERROR() << "query failed" << e.what();
        return false;
    }
}

QVariant SQLManagement::executeQuerySingleResult(const QString &query) {
    try {
        // 准备 SQL 查询
        std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(query.toStdString()));

        // 执行查询
        std::unique_ptr<sql::ResultSet> resultSet(pstmt->executeQuery());

        // 检查是否有结果
        if (resultSet->next()) {
            // 返回第一列的值
            return QString::fromStdString(resultSet->getString(1)); // 1-based index
        }
    } catch (sql::SQLException &e) {
        LOG_ERROR() << "query failed" << e.what();
    }

    return QVariant(); // 没有结果时返回无效的 QVariant
}


QVariantList SQLManagement::executeQueryResultList(const QString &query, const QString &profession) {
    QVariantList resultList; // 用于存储查询结果

    try {
        // 准备 SQL 查询
        std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(query.toStdString()));
        pstmt->setString(1, profession.toStdString()); // 设置参数

        // 执行查询并获取结果集
        std::unique_ptr<sql::ResultSet> resultSet(pstmt->executeQuery());

        // 遍历结果集
        while (resultSet->next()) {
            resultList.append(QString::fromStdString(resultSet->getString("username")));
        }
    } catch (sql::SQLException &e) {
        LOG_ERROR() << "Query failed: " << e.what();
    }

    return resultList; // 返回查询结果列表
}


QVariantMap SQLManagement::executeQueryResult(const QString &query, const QString &identity) {
    QVariantMap resultMap; // 用于存储查询结果

    try {
        // 准备 SQL 查询
        std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(query.toStdString()));

        // 执行查询并获取结果集
        std::unique_ptr<sql::ResultSet> resultSet(pstmt->executeQuery());

        // 检查是否有结果
        if (resultSet->next()) {
            // 获取各列的值并存入 QVariantMap
            resultMap["username"] = QString::fromStdString(resultSet->getString("username"));
            resultMap["password"] = QString::fromStdString(resultSet->getString("password"));
            resultMap["age"] = QString::fromStdString(resultSet->getString("age"));
            resultMap["gender"] = QString::fromStdString(resultSet->getString("gender"));
            resultMap["phone"] = QString::fromStdString(resultSet->getString("phone"));
            resultMap["address"] = QString::fromStdString(resultSet->getString("address"));
            if(identity == "doctor") {
                resultMap["profession"] = QString::fromStdString(resultSet->getString("profession"));
            }

        }
    } catch (sql::SQLException &e) {
        LOG_ERROR() << "Query failed: " << e.what();
    }

    return resultMap; // 返回查询结果或空的 QVariantMap
}

QVariantList SQLManagement::executeQueryResultList(const QString &query, QueryType type)
{
    QVariantList resultList; // 用于存储查询结果

    try {
        // 准备 SQL 查询
        std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(query.toStdString()));

        // 执行查询并获取结果集
        std::unique_ptr<sql::ResultSet> resultSet(pstmt->executeQuery());

        // 遍历结果集
        while (resultSet->next()) {
            QVariantMap record;

            switch (type) {
            case PatientID:
                record["patient_id"] = resultSet->getInt("patient_id"); // 只提取 patient_id
                break;

            case PatientDetails:
                record["username"] = QString::fromStdString(resultSet->getString("username"));
                record["age"] = resultSet->getInt("age");
                record["gender"] = QString::fromStdString(resultSet->getString("gender"));
                record["phone"] = QString::fromStdString(resultSet->getString("phone"));
                record["address"] = QString::fromStdString(resultSet->getString("address"));
                break;

            case DoctorDetails:
                record["username"] = QString::fromStdString(resultSet->getString("username"));
                record["profession"] = QString::fromStdString(resultSet->getString("profession"));
                record["age"] = resultSet->getInt("age");
                record["gender"] = QString::fromStdString(resultSet->getString("gender"));
                record["phone"] = QString::fromStdString(resultSet->getString("phone"));
                record["address"] = QString::fromStdString(resultSet->getString("address"));
                break;

            case InstructionDetails: // 添加对 InstructionDetails 的处理
                record["id"] = QString::fromStdString(resultSet->getString("id"));
                record["instruction"] = QString::fromStdString(resultSet->getString("instruction"));
                record["doctor_id"] = QString::fromStdString(resultSet->getString("doctor_id"));
                break;

            case MedicalRecordDetails: // 添加对 MedicalRecordDetails 的处理
                record["doctor_id"] = resultSet->getInt("doctor_id");
                record["record_date"] = QString::fromStdString(resultSet->getString("record_date"));
                record["disease_name"] = QString::fromStdString(resultSet->getString("disease_name"));
                record["details"] = QString::fromStdString(resultSet->getString("details"));
                break;

            case PrescriptionDetails: // 添加对 PrescriptionDetails 的处理
                record["advice_id"] = QString::fromStdString(resultSet->getString("advice_id"));
                record["medication_name"] = QString::fromStdString(resultSet->getString("medication_name"));
                record["dosage"] = QString::fromStdString(resultSet->getString("dosage"));
                record["frequency"] = QString::fromStdString(resultSet->getString("frequency"));
                record["cost"] = QString::fromStdString(resultSet->getString("cost"));
                break;

            case AdviceIdDetails: // 添加对 AdviceIdDetails 的处理
                record["id"] = resultSet->getInt("id"); // 提取医嘱 ID
                break;


            default:
                // 处理未知类型的情况
                LOG_ERROR() << "Unknown QueryType: " << type;
                break;
            }

            resultList.append(record); // 将整行数据添加到结果列表中
        }
    } catch (sql::SQLException &e) {
        LOG_ERROR() << "Query failed: " << e.what();
    }

    // LOG_DEBUG() << "executeQueryResultList result: " << resultList;
    return resultList; // 返回包含查询结果的列表
}
